﻿using System;
using System.Configuration;
using System.Data;
using System.Text;
using System.Web.UI;
using System.Collections;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

using Microsoft.Reporting.WebForms;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace dcwj
{
    public partial class jsdbjsPage : System.Web.UI.Page
    {
        private String M_CONSTR = ConfigurationManager.ConnectionStrings["dcwjConnectionString"].ToString();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                try
                {
                    ArrayList ar = Session["selList"] as ArrayList;
           
                    if (ar == null) Response.Redirect("");
                    Database objDB = new SqlDatabase(M_CONSTR);
                    
                    DataSet dsAllClass = new DataSet();
                    DataSet dsAllClass1 = new DataSet();
                    DataSet dsAllClass1Dtl = new DataSet();
                    DataSet dsAllClass2 = new DataSet();
                    DataSet dsAllClass2Dtl = new DataSet();
                    DataSet dsAllClass3 = new DataSet();
                    DataSet dsAllClass3Dtl = new DataSet();
                    DataSet dsAllClass4 = new DataSet();
                    DataSet dsAllClass4Dtl = new DataSet();
                    DataSet dsTeacherClass = new DataSet();

                    for (int i = 0; i < ar.Count; i++)
                    {

                        DataSet dsClass = objDB.ExecuteDataSet(CommandType.Text, @"select             
b.teachername,
a.score
from
(SELECT avg(cast (QAnswer as decimal)) as score FROM Table_RESULT where paperid =" + ar[i].ToString() + @") as a,
(SELECT teachername FROM Table_TEACHER where teacherid=
(select teacherid from table_paper where paperid=" + ar[i].ToString() + @" )) as b");

                        dsAllClass.Merge(dsClass);


                        DataSet dsClass1 = objDB.ExecuteDataSet(CommandType.Text, @"select             
b.teachername,
a.score
from
(SELECT avg(cast (QAnswer as decimal)) as score FROM Table_RESULT where paperid =" + ar[i].ToString() + @" and qid in(1,2,3,4,5,6,7,8)) as a,
(SELECT teachername FROM Table_TEACHER where teacherid=
(select teacherid from table_paper where paperid=" + ar[i].ToString() + @" )) as b");

                        dsAllClass1.Merge(dsClass1);


                        DataSet dsClass2 = objDB.ExecuteDataSet(CommandType.Text, @"select             
b.teachername,
a.score
from
(SELECT avg(cast (QAnswer as decimal)) as score FROM Table_RESULT where paperid =" + ar[i].ToString() + @" and qid in(9,10,11,12,13,14,15)) as a,
(SELECT teachername FROM Table_TEACHER where teacherid=
(select teacherid from table_paper where paperid=" + ar[i].ToString() + @" )) as b");

                        dsAllClass2.Merge(dsClass2);


                        DataSet dsClass3 = objDB.ExecuteDataSet(CommandType.Text, @"select             
b.teachername,
a.score
from
(SELECT avg(cast (QAnswer as decimal)) as score FROM Table_RESULT where paperid =" + ar[i].ToString() + @" and qid in(16,17,18)) as a,
(SELECT teachername FROM Table_TEACHER where teacherid=
(select teacherid from table_paper where paperid=" + ar[i].ToString() + @" )) as b");

                        dsAllClass3.Merge(dsClass3);


                        DataSet dsClass4 = objDB.ExecuteDataSet(CommandType.Text, @"select             
b.teachername,
a.score
from
(SELECT avg(cast (QAnswer as decimal)) as score FROM Table_RESULT where paperid =" + ar[i].ToString() + @" and qid in(19,20)) as a,
(SELECT teachername FROM Table_TEACHER where teacherid=
(select teacherid from table_paper where paperid=" + ar[i].ToString() + @" )) as b");

                        dsAllClass4.Merge(dsClass4);


                        DataSet dsClass1Dtl = objDB.ExecuteDataSet(CommandType.Text, @"select 
					p.teachername,
                    a.q1,
                    b.q2,
                    c.q3,
                    d.q4,
					e.q5,
					f.q6,
					g.q7,
					h.q8
                    from
					
					(SELECT teachername from Table_teacher where teacherid =(select teacherid from table_paper where paperid=" + ar[i].ToString() + @")) as p,

                    (SELECT 
                    avg(cast (QAnswer as decimal)) as q1
                      FROM Table_RESULT
                    where paperid =" + ar[i].ToString() + @" and Qid in(1)) as a,

                    (SELECT 
                    avg(cast (QAnswer as decimal)) as q2
                      FROM Table_RESULT
                    where paperid = " + ar[i].ToString() + @" and Qid in(2)) as b,

                    (SELECT 
                    avg(cast (QAnswer as decimal)) as q3
                      FROM Table_RESULT
                    where paperid = " + ar[i].ToString() + @" and Qid in(3)) as c,

                    (SELECT 
                    avg(cast (QAnswer as decimal)) as q4
                      FROM Table_RESULT
                    where paperid = " + ar[i].ToString() + @" and Qid in(4)) as d,


                    (SELECT 
                    avg(cast (QAnswer as decimal)) as q5
                      FROM Table_RESULT
                    where paperid = " + ar[i].ToString() + @" and Qid in(5)) as e,
					
					(SELECT 
                    avg(cast (QAnswer as decimal)) as q6
                      FROM Table_RESULT
                    where paperid = " + ar[i].ToString() + @" and Qid in(6)) as f,
					
					(SELECT 
                    avg(cast (QAnswer as decimal)) as q7
                      FROM Table_RESULT
                    where paperid = " + ar[i].ToString() + @" and Qid in(7)) as g,
					
					(SELECT 
                    avg(cast (QAnswer as decimal)) as q8
                      FROM Table_RESULT
                    where paperid = " + ar[i].ToString() + @" and Qid in(8)) as h");
                      
                        dsAllClass1Dtl.Merge(dsClass1Dtl);

                        DataSet dsClass2Dtl = objDB.ExecuteDataSet(CommandType.Text, @"select 
					p.teachername,
                    a.q9,
                    b.q10,
                    c.q11,
                    d.q12,
					e.q13,
					f.q14,
					g.q15
                    from
					
					(SELECT teachername from Table_teacher where teacherid =(select teacherid from table_paper where paperid=" + ar[i].ToString() + @")) as p,

                    (SELECT 
                    avg(cast (QAnswer as decimal)) as q9
                      FROM Table_RESULT
                    where paperid =" + ar[i].ToString() + @" and Qid in(9)) as a,

                    (SELECT 
                    avg(cast (QAnswer as decimal)) as q10
                      FROM Table_RESULT
                    where paperid = " + ar[i].ToString() + @" and Qid in(10)) as b,

                    (SELECT 
                    avg(cast (QAnswer as decimal)) as q11
                      FROM Table_RESULT
                    where paperid = " + ar[i].ToString() + @" and Qid in(11)) as c,

                    (SELECT 
                    avg(cast (QAnswer as decimal)) as q12
                      FROM Table_RESULT
                    where paperid = " + ar[i].ToString() + @" and Qid in(12)) as d,


                    (SELECT 
                    avg(cast (QAnswer as decimal)) as q13
                      FROM Table_RESULT
                    where paperid = " + ar[i].ToString() + @" and Qid in(13)) as e,
					
					(SELECT 
                    avg(cast (QAnswer as decimal)) as q14
                      FROM Table_RESULT
                    where paperid = " + ar[i].ToString() + @" and Qid in(14)) as f,
					
					(SELECT 
                    avg(cast (QAnswer as decimal)) as q15
                      FROM Table_RESULT
                    where paperid = " + ar[i].ToString() + @" and Qid in(15)) as g");

                        dsAllClass2Dtl.Merge(dsClass2Dtl);

                        DataSet dsClass3Dtl = objDB.ExecuteDataSet(CommandType.Text, @"select 
					p.teachername,
                    a.q16,
                    b.q17,
                    c.q18
                  
                    from
					
					(SELECT teachername from Table_teacher where teacherid =(select teacherid from table_paper where paperid=" + ar[i].ToString() + @")) as p,

                    (SELECT 
                    avg(cast (QAnswer as decimal)) as q16
                      FROM Table_RESULT
                    where paperid =" + ar[i].ToString() + @" and Qid in(16)) as a,

                    (SELECT 
                    avg(cast (QAnswer as decimal)) as q17
                      FROM Table_RESULT
                    where paperid = " + ar[i].ToString() + @" and Qid in(17)) as b,

                    (SELECT 
                    avg(cast (QAnswer as decimal)) as q18
                      FROM Table_RESULT
                    where paperid = " + ar[i].ToString() + @" and Qid in(18)) as c");

                        dsAllClass3Dtl.Merge(dsClass3Dtl);


                        DataSet dsClass4Dtl = objDB.ExecuteDataSet(CommandType.Text, @"select 
					p.teachername,
                    a.q19,
                    b.q20
                    from
					
					(SELECT teachername from Table_teacher where teacherid =(select teacherid from table_paper where paperid=" + ar[i].ToString() + @")) as p,

                    (SELECT 
                    avg(cast (QAnswer as decimal)) as q19
                      FROM Table_RESULT
                    where paperid =" + ar[i].ToString() + @" and Qid in(19)) as a,

                    (SELECT 
                    avg(cast (QAnswer as decimal)) as q20
                      FROM Table_RESULT
                    where paperid = " + ar[i].ToString() + @" and Qid in(20)) as b");

                        dsAllClass4Dtl.Merge(dsClass4Dtl);
                    }


                    jsdbjsDS ds = new jsdbjsDS();
                    for (int j = 0; j < ar.Count; j++)
                    {
                        DataSet dsTmp = objDB.ExecuteDataSet(CommandType.Text, @"select 
t.TeacherName,
p.PaperName from Table_PAPER p, Table_TEACHER t
where p.PaperID=" + ar[j].ToString() + " and p.teacherid = t.teacherid");
                        ds.TeacherClass.AddTeacherClassRow(dsTmp.Tables[0].Rows[0][0].ToString(),
                            dsTmp.Tables[0].Rows[0][1].ToString());
                    }
                    if (dsAllClass != null && dsAllClass.Tables[0].Rows.Count > 0)
                    {
                        for (int i = 0; i < dsAllClass.Tables[0].Rows.Count; i++)
                        {
                            ds.TeacherAll.AddTeacherAllRow(dsAllClass.Tables[0].Rows[i][0].ToString(), 
                                dsAllClass.Tables[0].Rows[i][1].ToString());
                        }
                    }

                    if (dsAllClass1 != null && dsAllClass1.Tables[0].Rows.Count > 0)
                    {
                        for (int i = 0; i < dsAllClass1.Tables[0].Rows.Count; i++)
                        {
                            ds.Teacher1.AddTeacher1Row(dsAllClass1.Tables[0].Rows[i][0].ToString(),
                                 dsAllClass1.Tables[0].Rows[i][1].ToString());
                        }
                    }


                    if (dsAllClass2 != null && dsAllClass2.Tables[0].Rows.Count > 0)
                    {
                        for (int i = 0; i < dsAllClass2.Tables[0].Rows.Count; i++)
                        {
                            ds.Teacher2.AddTeacher2Row(dsAllClass2.Tables[0].Rows[i][0].ToString(),
                                 dsAllClass2.Tables[0].Rows[i][1].ToString());
                        }
                    }

                    if (dsAllClass3 != null && dsAllClass3.Tables[0].Rows.Count > 0)
                    {
                        for (int i = 0; i < dsAllClass3.Tables[0].Rows.Count; i++)
                        {
                            ds.Teacher3.AddTeacher3Row(dsAllClass3.Tables[0].Rows[i][0].ToString(),
                                  dsAllClass3.Tables[0].Rows[i][1].ToString());
                        }
                    }

                    if (dsAllClass4 != null && dsAllClass4.Tables[0].Rows.Count > 0)
                    {
                        for (int i = 0; i < dsAllClass4.Tables[0].Rows.Count; i++)
                        {
                            ds.Teacher4.AddTeacher4Row(dsAllClass4.Tables[0].Rows[i][0].ToString(),
                                 dsAllClass4.Tables[0].Rows[i][1].ToString());
                        }
                    }

                    if (dsAllClass1Dtl != null && dsAllClass1Dtl.Tables[0].Rows.Count > 0)
                    {
                        for (int i = 0; i < dsAllClass1Dtl.Tables[0].Rows.Count; i++)
                        {
                            ds.Teacher1Dtl.AddTeacher1DtlRow(
                                dsAllClass1Dtl.Tables[0].Rows[i][0].ToString(),
                                dsAllClass1Dtl.Tables[0].Rows[i][1].ToString(),
                                "熟悉课程内容，讲解流畅");
                            ds.Teacher1Dtl.AddTeacher1DtlRow(
                             dsAllClass1Dtl.Tables[0].Rows[i][0].ToString(),
                             dsAllClass1Dtl.Tables[0].Rows[i][2].ToString(),
                             "讲解能够围绕课程主题");
                            ds.Teacher1Dtl.AddTeacher1DtlRow(
                             dsAllClass1Dtl.Tables[0].Rows[i][0].ToString(),
                             dsAllClass1Dtl.Tables[0].Rows[i][3].ToString(),
                             "能够根据培训目标完成各课程单元的讲解，有课程小结");
                            ds.Teacher1Dtl.AddTeacher1DtlRow(
                             dsAllClass1Dtl.Tables[0].Rows[i][0].ToString(),
                             dsAllClass1Dtl.Tables[0].Rows[i][4].ToString(),
                             "对案例的讲解分析合理恰当");
                            ds.Teacher1Dtl.AddTeacher1DtlRow(
                             dsAllClass1Dtl.Tables[0].Rows[i][0].ToString(),
                             dsAllClass1Dtl.Tables[0].Rows[i][5].ToString(),
                             "对方法、技巧的示范明了易懂");
                            ds.Teacher1Dtl.AddTeacher1DtlRow(
                             dsAllClass1Dtl.Tables[0].Rows[i][0].ToString(),
                             dsAllClass1Dtl.Tables[0].Rows[i][6].ToString(),
                             "授课教具准备充分");
                            ds.Teacher1Dtl.AddTeacher1DtlRow(
                             dsAllClass1Dtl.Tables[0].Rows[i][0].ToString(),
                             dsAllClass1Dtl.Tables[0].Rows[i][7].ToString(),
                             "能够根据课程重难点合理安排课程讲解和课堂活动时间");
                            ds.Teacher1Dtl.AddTeacher1DtlRow(
                             dsAllClass1Dtl.Tables[0].Rows[i][0].ToString(),
                             dsAllClass1Dtl.Tables[0].Rows[i][8].ToString(),
                             "能够准时开课、下课");
                        }
                    }

                    if (dsAllClass2Dtl != null && dsAllClass1Dtl.Tables[0].Rows.Count > 0)
                    {
                        for (int i = 0; i < dsAllClass1Dtl.Tables[0].Rows.Count; i++)
                        {
                            ds.Teacher2Dtl.AddTeacher2DtlRow(
                                dsAllClass2Dtl.Tables[0].Rows[i][0].ToString(),
                                dsAllClass2Dtl.Tables[0].Rows[i][1].ToString(),
                                "经常提问引导学员学习");
                            ds.Teacher2Dtl.AddTeacher2DtlRow(
                             dsAllClass2Dtl.Tables[0].Rows[i][0].ToString(),
                             dsAllClass2Dtl.Tables[0].Rows[i][2].ToString(),
                             "经常鼓励学员发言、提问");
                            ds.Teacher2Dtl.AddTeacher2DtlRow(
                             dsAllClass2Dtl.Tables[0].Rows[i][0].ToString(),
                             dsAllClass2Dtl.Tables[0].Rows[i][3].ToString(),
                             "能够活跃课堂气氛、控制课堂秩序");
                            ds.Teacher2Dtl.AddTeacher2DtlRow(
                             dsAllClass2Dtl.Tables[0].Rows[i][0].ToString(),
                             dsAllClass2Dtl.Tables[0].Rows[i][4].ToString(),
                             "能够引导不同学习态度学员，调动全员参与");
                            ds.Teacher2Dtl.AddTeacher2DtlRow(
                             dsAllClass2Dtl.Tables[0].Rows[i][0].ToString(),
                             dsAllClass2Dtl.Tables[0].Rows[i][5].ToString(),
                             "授课语音、语调适当，表达清晰易懂");
                            ds.Teacher2Dtl.AddTeacher2DtlRow(
                             dsAllClass2Dtl.Tables[0].Rows[i][0].ToString(),
                             dsAllClass2Dtl.Tables[0].Rows[i][6].ToString(),
                             "能将个人行业经验与课程讲授的结合");
                            ds.Teacher2Dtl.AddTeacher2DtlRow(
                             dsAllClass2Dtl.Tables[0].Rows[i][0].ToString(),
                             dsAllClass2Dtl.Tables[0].Rows[i][7].ToString(),
                             "能够配合课程内容讲授恰当案例");
                        }
                    }

                    if (dsAllClass3Dtl != null && dsAllClass3Dtl.Tables[0].Rows.Count > 0)
                    {
                        for (int i = 0; i < dsAllClass3Dtl.Tables[0].Rows.Count; i++)
                        {
                            ds.Teacher3Dtl.AddTeacher3DtlRow(
                                dsAllClass3Dtl.Tables[0].Rows[i][0].ToString(),
                                dsAllClass3Dtl.Tables[0].Rows[i][1].ToString(),
                                "授课精神饱满有热情");
                            ds.Teacher3Dtl.AddTeacher3DtlRow(
                             dsAllClass3Dtl.Tables[0].Rows[i][0].ToString(),
                             dsAllClass3Dtl.Tables[0].Rows[i][2].ToString(),
                             "讲解细致认真 ");
                            ds.Teacher3Dtl.AddTeacher3DtlRow(
                             dsAllClass3Dtl.Tables[0].Rows[i][0].ToString(),
                             dsAllClass3Dtl.Tables[0].Rows[i][3].ToString(),
                             "对学员回答问题后的反应积极（肯定，表扬，启发等）");
                        }
                    }

                    if (dsAllClass4Dtl != null && dsAllClass4Dtl.Tables[0].Rows.Count > 0)
                    {
                        for (int i = 0; i < dsAllClass4Dtl.Tables[0].Rows.Count; i++)
                        {
                            ds.Teacher4Dtl.AddTeacher4DtlRow(
                                dsAllClass4Dtl.Tables[0].Rows[i][0].ToString(),
                                dsAllClass4Dtl.Tables[0].Rows[i][1].ToString(),
                                "对学员提问的解答准确，专业");
                            ds.Teacher4Dtl.AddTeacher4DtlRow(
                             dsAllClass4Dtl.Tables[0].Rows[i][0].ToString(),
                             dsAllClass4Dtl.Tables[0].Rows[i][2].ToString(),
                             "讲解有深度，能够启发学员思考");
                     
                        }
                    }

                    ReportDataSource rds0 = new ReportDataSource("TeacherAll", ds.TeacherAll.Select("","得分 desc"));
                    ReportDataSource rds1 = new ReportDataSource("Teacher1", ds.Teacher1.Select("", "得分 desc"));
                    ReportDataSource rds2 = new ReportDataSource("Teacher2", ds.Teacher2.Select("", "得分 desc"));
                    ReportDataSource rds3 = new ReportDataSource("Teacher3", ds.Teacher3.Select("", "得分 desc"));
                    ReportDataSource rds4 = new ReportDataSource("Teacher4", ds.Teacher4.Select("", "得分 desc"));
                    ReportDataSource rds5 = new ReportDataSource("Teacher1Dtl", (DataTable)ds.Teacher1Dtl);
                    ReportDataSource rds6 = new ReportDataSource("Teacher2Dtl", (DataTable)ds.Teacher2Dtl);
                    ReportDataSource rds7 = new ReportDataSource("Teacher3Dtl", (DataTable)ds.Teacher3Dtl);
                    ReportDataSource rds8 = new ReportDataSource("Teacher4Dtl", (DataTable)ds.Teacher4Dtl);
                    ReportDataSource rds9 = new ReportDataSource("TeacherClass", (DataTable)ds.TeacherClass);

                   

                    ReportViewer1.LocalReport.DataSources.Add(rds0);
                    ReportViewer1.LocalReport.DataSources.Add(rds1);
                    ReportViewer1.LocalReport.DataSources.Add(rds2);
                    ReportViewer1.LocalReport.DataSources.Add(rds3);
                    ReportViewer1.LocalReport.DataSources.Add(rds4);
                    ReportViewer1.LocalReport.DataSources.Add(rds5);
                    ReportViewer1.LocalReport.DataSources.Add(rds6);
                    ReportViewer1.LocalReport.DataSources.Add(rds7);
                    ReportViewer1.LocalReport.DataSources.Add(rds8);
                    ReportViewer1.LocalReport.DataSources.Add(rds9);
                    
                    ReportViewer1.LocalReport.Refresh();

                    Session["selList"] = null;
                }
                catch (Exception ex)
                {
                    Response.Write(ex.Message);
                }
            }
        }
    }
}